﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;

namespace Obsidian.Data.Sql.MySqlClient
{
    /// <summary>
    /// 提供执行数据访问
    /// author:郑金泉
    /// </summary>
    public sealed class MySqlExecutor
    {

        #region #

        private string _connectionString = null;

        private static Hashtable mySqlParametersCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 设置Command对象
        /// </summary>
        /// <param name="commandType">执行类型</param>
        /// <param name="commandText">SQL文本</param>
        /// <param name="transaction">事务</param>
        private static void PrepareCommand(MySqlCommand command, CommandType commandType, string commandText, MySqlParameter[] mySqlParams, MySqlTransaction transaction)
        {
            command.CommandType = commandType;

            if (String.IsNullOrEmpty(commandText))
                throw new ArgumentNullException("commandText");
            else
                command.CommandText = commandText;

            if (mySqlParams != null)
            {
                foreach (MySqlParameter p in mySqlParams)
                {
                    command.Parameters.Add(p);
                }
            }

            if (transaction != null)
                command.Transaction = transaction;

            MySqlConnection connection = command.Connection;
            if (connection.State != ConnectionState.Open)
                connection.Open();
        }

        public string ConnectionString
        {
            get { return this._connectionString; }
            set { this._connectionString = value; }
        }

        #endregion

        #region 参数缓存

        /// <summary>
        /// 重新得到缓存中的参数
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static MySqlParameter[] GetCacheParameters(string key)
        {
            MySqlParameter[] cachedParams = (MySqlParameter[])mySqlParametersCache[key];

            if (cachedParams == null)
                return null;

            MySqlParameter[] clonedParams = new MySqlParameter[cachedParams.Length];

            for (int i = 0, j = clonedParams.Length; i < j; i++)
                clonedParams[i] = (MySqlParameter)((ICloneable)cachedParams[i]).Clone();

            return clonedParams;
        }

        /// <summary>
        /// 加载参数数组到缓存
        /// </summary>
        /// <param name="key"></param>
        /// <param name="sqlParams"></param>
        public static void SetCacheParameters(string key, MySqlParameter[] mySqlParams)
        {
            if (key == null || String.IsNullOrEmpty(key))
                throw new ArgumentNullException("key");
            if (mySqlParams == null)
                throw new ArgumentNullException("mySqlParams");

            mySqlParametersCache[key] = mySqlParams;
        }

        #endregion

        #region 执行并返回受影响的行数

        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(string commandText)
        {
            return SqlExecuteReader(CommandType.Text, commandText, null);
        }

        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return SqlExecuteReader(commandType, commandText, null);
        }

        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(string commandText, MySqlParameter[] mySqlParams)
        {
            return SqlExecuteReader(CommandType.Text, commandText, mySqlParams);
        }

        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText, MySqlParameter[] mySqlParams)
        {
            return SqlExecuteReader(commandType, commandText, mySqlParams);
        }

        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int SqlExecuteReader(CommandType commandType, string commandText, MySqlParameter[] mySqlParams)
        {
            int n;
            using (MySqlConnection connection = new MySqlConnection(this._connectionString))
            {
                MySqlCommand command = connection.CreateCommand();
                PrepareCommand(command, commandType, commandText, mySqlParams, null);
                try { n = command.ExecuteNonQuery(); }
                catch { throw; }
                finally
                {
                    command.Parameters.Clear();
                    command.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            return n;
        }

        #endregion

        #region 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        public object ExecuteScalar(string commandText)
        {
            return SqlExecuteScalar(CommandType.Text, commandText, null);
        }

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            return SqlExecuteScalar(commandType, commandText, null);
        }

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        public object ExecuteScalar(string commandText, MySqlParameter[] mySqlParams)
        {
            return SqlExecuteScalar(CommandType.Text, commandText, mySqlParams);
        }

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        public object ExecuteScalar(CommandType commandType, string commandText, MySqlParameter[] mySqlParams)
        {
            return SqlExecuteScalar(commandType, commandText, mySqlParams);
        }

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        private object SqlExecuteScalar(CommandType commandType, string commandText, MySqlParameter[] mySqlParams)
        {
            object obj;
            using (MySqlConnection connection = new MySqlConnection(this._connectionString))
            {
                MySqlCommand command = connection.CreateCommand();
                PrepareCommand(command, commandType, commandText, mySqlParams, null);
                try { obj = command.ExecuteScalar(); }
                catch { throw; }
                finally
                {
                    command.Parameters.Clear();
                    command.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            return obj;
        }

        #endregion

        #region 执行并返回DataSet

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string commandText)
        {
            return SqlExecuteDataSet(CommandType.Text, commandText, null);
        }

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(CommandType commandType, string commandText)
        {
            return SqlExecuteDataSet(commandType, commandText, null);
        }

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string commandText, MySqlParameter[] mySqlParams)
        {
            return SqlExecuteDataSet(CommandType.Text, commandText, mySqlParams);
        }

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(CommandType commandType, string commandText, MySqlParameter[] mySqlParams)
        {
            return SqlExecuteDataSet(commandType, commandText, mySqlParams);
        }

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        private DataSet SqlExecuteDataSet(CommandType commandType, string commandText, MySqlParameter[] mySqlParams)
        {
            DataSet dataSet = new DataSet();
            using (MySqlConnection connection = new MySqlConnection(this._connectionString))
            {
                MySqlCommand command = connection.CreateCommand();
                PrepareCommand(command, commandType, commandText, mySqlParams, null);
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
                try { dataAdapter.Fill(dataSet); }
                catch
                {
                    dataSet.Dispose();
                    throw;
                }
                finally
                {
                    dataAdapter.Dispose();
                    command.Parameters.Clear();
                    command.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            return dataSet;
        }

        #endregion

        #region 执行并返回DataReader

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public MySqlDataReader ExecuteReader(string commandText)
        {
            return SqlExecuteReader(CommandType.Text, commandText, null, CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public MySqlDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            return SqlExecuteReader(commandType, commandText, null, CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public MySqlDataReader ExecuteReader(string commandText, MySqlParameter[] mySqlParams)
        {
            return SqlExecuteReader(CommandType.Text, commandText, mySqlParams, CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public MySqlDataReader ExecuteReader(CommandType commandType, string commandText, MySqlParameter[] mySqlParams)
        {
            return SqlExecuteReader(commandType, commandText, mySqlParams, CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public MySqlDataReader ExecuteReader(string commandText, CommandBehavior behavior)
        {
            return SqlExecuteReader(CommandType.Text, commandText, null, behavior);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public MySqlDataReader ExecuteReader(CommandType commandType, string commandText, CommandBehavior behavior)
        {
            return SqlExecuteReader(commandType, commandText, null, behavior);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public MySqlDataReader ExecuteReader(string commandText, MySqlParameter[] mySqlParams, CommandBehavior behavior)
        {
            return SqlExecuteReader(CommandType.Text, commandText, mySqlParams, behavior);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public MySqlDataReader ExecuteReader(CommandType commandType, string commandText, MySqlParameter[] mySqlParams, CommandBehavior behavior)
        {
            return SqlExecuteReader(commandType, commandText, mySqlParams, behavior);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        private MySqlDataReader SqlExecuteReader(CommandType commandType, string commandText, MySqlParameter[] mySqlParams, CommandBehavior behavior)
        {
            MySqlDataReader dataReader;
            MySqlConnection connection = new MySqlConnection(this._connectionString);
            MySqlCommand command = connection.CreateCommand();
            PrepareCommand(command, commandType, commandText, mySqlParams, null);
            try { dataReader = command.ExecuteReader(behavior); }
            catch { throw; }
            finally
            {
                command.Parameters.Clear();
                command.Dispose();
            }
            return dataReader;
        }

        #endregion


        public MySqlCommand BeginTransaction(CommandType commandType, MySqlParameter[] dbParams, string commandText, out MySqlTransaction transaction)
        {
            MySqlConnection connection = new MySqlConnection(this._connectionString);
            MySqlCommand command = connection.CreateCommand();
            PrepareCommand(command, commandType, commandText, dbParams, null);
            transaction = connection.BeginTransaction();
            command.Transaction = transaction;
            return command;
        }

        #region 读取表结构
        /*
        public static DBTable GetTableSchema(string name)
        {
            using (MySqlConnection connection = new MySqlConnection(mySqlConnectionString))
            {
                connection.Open();
                DataTable table = connection.GetSchema("Columns", new string[] { null, null, name, null });

                if (table.Rows.Count == 0)
                    throw new Exception("找不到映射表 - " + name);
                List<DBField> fieldList = new List<DBField>();
                DBTable dbt = new DBTable(name);
                foreach (DataRow r in table.Rows)
                {
                    string columnName = r[3].ToString();
                    object defaultValue = r[5];
                    bool allowNull = r[6].ToString() == "YES";
                    string type = r[7].ToString();
                    int size = Convert.IsDBNull(r[8]) ? -1 : Convert.ToInt32(r[8]);
                    bool isPK = r[14].ToString() == "PRI";
                    bool isUNI = isPK ? isPK : (r[14].ToString() == "UNI");
                    bool autoIncrement = r[15].ToString() == "auto_increment";
                    DBField f = new DBField(
                        dbt,
                        columnName,
                        isPK,
                        allowNull,
                        autoIncrement,
                        isUNI,
                        defaultValue,
                        ToDataType(type),
                        size);
                    fieldList.Add(f);
                }
                connection.Clone();
                dbt.SetFields(fieldList.ToArray());
                return dbt;
            }
        }

        private static DataType ToDataType(string type)
        {
            switch (type.ToLower())
            {
                case "bit":
                    return DataType.Bit;
                case "int":
                    return DataType.Int;
                case "bigint":
                    return DataType.Bigint;
                case "varchar":
                    return DataType.VarChar;
                case "text":
                    return DataType.Text;
                case "date":
                    return DataType.DateTime;
                case "datetime":
                    return DataType.DateTime;
                case "decimal":
                    return DataType.Decimal;
                default:
                    throw new Exception("找不到对应类型 - " + type);
            }
        }
        */
        #endregion

    }
}
